from sqlalchemy import Column, Integer, String, Boolean, DateTime, ForeignKey, Text, UniqueConstraint, Index
from datetime import datetime
from app.db.database import Base
from sqlalchemy.orm import relationship
baseconfig={
            'mysql_engine': 'InnoDB',
            'mysql_charset': 'utf8mb4',
            'mysql_collate': 'utf8mb4_unicode_ci'
        }
class User(Base):
    __tablename__ = 'users'
    __table_args__ = (
        Index('idx_users_online', 'online'),
        Index('idx_users_last_active', 'last_at'),
        Index('idx_users_userid', 'userid'), 
        baseconfig
    )
    
    id = Column(Integer, primary_key=True)
    userid = Column(String(36), unique=True, nullable=False)  # 业务ID，可以使用UUID或雪花ID
    email = Column(String(100), unique=True, nullable=False, index=True)
    password = Column(String(100), nullable=False)
    nickname = Column(String(50), nullable=False)
    avatar = Column(String(255), default='')
    last_at = Column(DateTime)
    created_at = Column(DateTime, default=datetime.now)
    online = Column(Boolean, default=False)
    device_fingerprint = Column(String(32), default='')
    sex=Column(Integer, default=0)
    friends = relationship(
        "Friend", 
        back_populates="user",
        foreign_keys="[Friend.user_id]"  # 明确指定使用哪个外键
    )
    friend_groups = relationship("FriendGroup", back_populates="user")

class Friend(Base):
    __tablename__ = 'friends'
    __table_args__ = (
        UniqueConstraint('user_id', 'friend_id', name='uix_user_friend'),
        Index('idx_friends_dual', 'user_id', 'friend_id', 'friend_group_id'),  # 更新索引字段
        Index('idx_friends_status', 'status'),
        Index('idx_friends_pinned', 'is_pinned'),
        baseconfig
    )
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    friend_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    friend_group_id = Column(Integer, ForeignKey('friend_groups.id'), nullable=False)
    remark = Column(String(50), default='')
    is_pinned = Column(Boolean, default=False)
    status = Column(Integer, default=1)  # 1-正常 2-拉黑 3-删除
    created_at = Column(DateTime, default=datetime.now)

    user = relationship(
        "User", 
        foreign_keys=[user_id], 
        back_populates="friends"
    )
    friend = relationship("User", foreign_keys=[friend_id])
    friend_group = relationship("FriendGroup", back_populates="friends")

class Group(Base):
    __tablename__ = 'groups'
    __table_args__ = (
        baseconfig
    )
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    is_default = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.now)

    # 移除与User的直接关联
    # 添加与FriendGroup的关联
    friend_groups = relationship("FriendGroup", back_populates="group")

class FriendGroup(Base):
    __tablename__ = 'friend_groups'
    __table_args__ = (
        baseconfig
    )
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    group_id = Column(Integer, ForeignKey('groups.id'), nullable=False)
    created_at = Column(DateTime, default=datetime.now)

    user = relationship("User", back_populates="friend_groups")
    group = relationship("Group", back_populates="friend_groups")
    friends = relationship("Friend", back_populates="friend_group")  # 添加反向关系

class Apply(Base):
    __tablename__ = 'applies'
    __table_args__ = (
        Index('idx_applies_status', 'status'),
        baseconfig
    )
    
    id = Column(Integer, primary_key=True)
    applicant_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    target_id = Column(Integer, ForeignKey('users.id'))
    room_id = Column(Integer, ForeignKey('rooms.id'))
    content = Column(String(255), default='')
    type = Column(Integer, nullable=False)  # 1-好友申请 2-群聊申请
    status = Column(Integer, default=0)      # 0-待审核 1-已通过 2-已拒绝
    created_at = Column(DateTime, default=datetime.now)

    applicant = relationship("User", foreign_keys=[applicant_id])
    target = relationship("User", foreign_keys=[target_id])